use fieldbook 
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StorageTranaction')
BEGIN 
 DROP TABLE StorageTranaction
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldWorkOperation')
BEGIN 
 DROP TABLE FieldWorkOperation
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldWorkResource')
BEGIN 
 DROP TABLE FieldWorkResource
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Operation')
BEGIN 
 DROP TABLE Operation
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'OperationCategory')
BEGIN 
 DROP TABLE OperationCategory
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldWork')
BEGIN 
 DROP TABLE FieldWork
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldWorkCategory')
BEGIN 
 DROP TABLE FieldWorkCategory
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Storage')
BEGIN 
 DROP TABLE Storage
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Resource')
BEGIN 
 DROP TABLE Resource
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ResourceCategory')
BEGIN 
 DROP TABLE ResourceCategory
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FieldLeasing')
BEGIN 
 DROP TABLE FieldLeasing
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Message')
BEGIN 
 DROP TABLE Message
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Household')
BEGIN 
 DROP TABLE Household
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Field')
BEGIN 
 DROP TABLE Field
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AppUser')
BEGIN 
 DROP TABLE AppUser
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Role')
BEGIN 
 DROP TABLE Role
END
GO 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MeasureUnit')
BEGIN 
 DROP TABLE MeasureUnit
END
go

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MeasureCategory')
BEGIN 
 DROP TABLE MeasureCategory
END
go

/* Create table AppUser */
CREATE TABLE dbo.AppUser
	(
	UserID bigint NOT NULL IDENTITY (1, 1),
	RoleID int NOT NULL,
	UserName varchar(20) NULL,
	Password varchar(50) NULL,
	JMBG varchar(15) NOT NULL,
	FirstName nvarchar(50) NULL,
	LastName nvarchar(50) NULL,
	StreetName nvarchar(50) NOT NULL,
	StreetNumber varchar(5) NOT NULL, 
	PostalCode nvarchar(10) NOT NULL,
	City nvarchar(50) NOT NULL,
	EmailAdress varchar(50) NULL,
	ReceiveMail bit NOT NULL DEFAULT (0),
	PhoneNo varchar(50) NULL,
	Birthdate datetime NULL,
	Status int NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.AppUser ADD CONSTRAINT
	PK_AppUser PRIMARY KEY CLUSTERED 
	(
	UserID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/* Create table MeasureUnit */
CREATE TABLE dbo.MeasureUnit
	(
	MeasureUnitID int NOT NULL IDENTITY (1, 1),
	CategoryID int NOT NULL,
	Description varchar(20) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.MeasureUnit ADD CONSTRAINT
	PK_MeasureUnit PRIMARY KEY CLUSTERED 
	(
	MeasureUnitID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/* Create table field */ 
CREATE TABLE dbo.Field
	(
	FieldID bigint NOT NULL IDENTITY (1, 1),
	Cataster varchar(20) NOT NULL,
	OwnerID bigint NULL,
	MeasureUnitID int NOT NULL, 
	Area decimal (19, 2) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Field ADD CONSTRAINT
	PK_Field PRIMARY KEY CLUSTERED 
	(
	FieldID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Field ADD CONSTRAINT
	FK_Field_Owner FOREIGN KEY
	(
	OwnerID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Field ADD CONSTRAINT
	FK_Field_MeasureUnit FOREIGN KEY
	(
	MeasureUnitID
	) REFERENCES dbo.MeasureUnit
	(
	MeasureUnitID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table household */
CREATE TABLE dbo.Household
	(
	HeadID bigint NOT NULL,
	MemberID bigint NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Household ADD CONSTRAINT
	PK_Household PRIMARY KEY CLUSTERED 
	(
	HeadID ASC, MemberID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Household ADD CONSTRAINT
	FK_Household_Head FOREIGN KEY
	(
	HeadID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Household ADD CONSTRAINT
	FK_Household_Member FOREIGN KEY
	(
	MemberID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table Message */
CREATE TABLE dbo.Message
	(
	MessageID bigint IDENTITY(1,1) NOT NULL,
	SenderID bigint NULL,
	ReceiverID bigint NOT NULL,
	Caption nvarchar (255),
	Content ntext,
	MessageTypeID tinyint NOT NULL,
	MessagestatusID tinyint NOT NULL,
	MessageReadStatus bit NOT NULL DEFAULT(0)
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Message ADD CONSTRAINT
	PK_Message PRIMARY KEY CLUSTERED 
	(
	MessageID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Message ADD CONSTRAINT
	FK_Message_Sender FOREIGN KEY
	(
	SenderID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Message ADD CONSTRAINT
	FK_Message_Receiver FOREIGN KEY
	(
	ReceiverID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table FieldLeasing */
CREATE TABLE dbo.FieldLeasing
	(
	LeasingID bigint IDENTITY(1,1) NOT NULL,
	FieldID bigint NOT NULL,
	LeaseeID bigint NOT NULL,
	Year int NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.FieldLeasing ADD CONSTRAINT
	PK_FieldLeasing PRIMARY KEY CLUSTERED 
	(
	LeasingID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.FieldLeasing ADD CONSTRAINT
	FK_FieldLeasing_Field FOREIGN KEY
	(
	FieldID
	) REFERENCES dbo.Field
	(
	FieldID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.FieldLeasing ADD CONSTRAINT
	FK_FieldLeasing_AppUser FOREIGN KEY
	(
	LeaseeID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table resource category */
CREATE TABLE dbo.ResourceCategory
	(
	CategoryID bigint IDENTITY(1,1) NOT NULL,
	Description nvarchar(50) NOT NULL,
	HouseHoldID bigint NULL,
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.ResourceCategory ADD CONSTRAINT
	PK_ResourceCategory PRIMARY KEY CLUSTERED 
	(
	CategoryID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.ResourceCategory ADD CONSTRAINT
	FK_ResourceCategory_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table Resource */
CREATE TABLE dbo.Resource
	(
	ResourceID bigint IDENTITY(1,1) NOT NULL,
	CategoryID bigint NOT NULL,
	Description nvarchar(50) NOT NULL,
	HouseHoldID bigint NULL,
	MeasureUnitID int NOT NULL,
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Resource ADD CONSTRAINT
	PK_Resource PRIMARY KEY CLUSTERED 
	(
	ResourceID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Resource ADD CONSTRAINT
	FK_Resource_ResourceCategory FOREIGN KEY
	(
	CategoryID
	) REFERENCES dbo.ResourceCategory
	(
	CategoryID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Resource ADD CONSTRAINT
	FK_Resource_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Resource ADD CONSTRAINT
	FK_Resource_MeasureUnit FOREIGN KEY
	(
	MeasureUnitID
	) REFERENCES dbo.MeasureUnit
	(
	MeasureUnitID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table Storage */
CREATE TABLE dbo.Storage
	(
	StorageID bigint IDENTITY(1,1) NOT NULL,
	ResourceID bigint NOT NULL,
	HouseHoldID bigint NOT NULL,
	Quota decimal(19, 2) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Storage ADD CONSTRAINT
	PK_Storage PRIMARY KEY CLUSTERED 
	(
	StorageID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Storage ADD CONSTRAINT
	FK_Storage_Resource FOREIGN KEY
	(
	ResourceID
	) REFERENCES dbo.Resource
	(
	ResourceID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Storage ADD CONSTRAINT
	FK_Storage_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table FiledWork category */
CREATE TABLE dbo.FieldWorkCategory
	(
	CategoryID bigint IDENTITY(1,1) NOT NULL,
	Description nvarchar(50) NOT NULL,
	HouseHoldID bigint NULL,
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWorkCategory ADD CONSTRAINT
	PK_FieldWorkCategory PRIMARY KEY CLUSTERED 
	(
	CategoryID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWorkCategory ADD CONSTRAINT
	FK_FieldWorkCategory_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table FieldWork */
CREATE TABLE dbo.FieldWork
	(
	FieldWorkID bigint IDENTITY(1,1) NOT NULL,
	FieldID bigint NOT NULL,
	ExecutorID bigint NOT NULL,
	LoggerID bigint NOT NULL,
	ExecuionDate datetime NOT NULL,
	LoggedDate datetime NOT NULL,
	TotalCost decimal (19, 2) NOT NULL,
	Remarks nvarchar (1024) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWork ADD CONSTRAINT
	PK_FieldWork PRIMARY KEY CLUSTERED 
	(
	FieldWorkID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWork ADD CONSTRAINT
	FK_Resource_Field FOREIGN KEY
	(
	FieldID
	) REFERENCES dbo.Field
	(
	FieldID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.FieldWork ADD CONSTRAINT
	FK_Resource_Executor FOREIGN KEY
	(
	ExecutorID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.FieldWork ADD CONSTRAINT
	FK_Resource_Logger FOREIGN KEY
	(
	LoggerID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table operation category */
CREATE TABLE dbo.OperationCategory
	(
	CategoryID bigint IDENTITY(1,1) NOT NULL,
	Description nvarchar(50) NOT NULL,
	HouseHoldID bigint NULL,
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.OperationCategory ADD CONSTRAINT
	PK_OperationCategory PRIMARY KEY CLUSTERED 
	(
	CategoryID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.OperationCategory ADD CONSTRAINT
	FK_OperationCategory_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table Operation */
CREATE TABLE dbo.Operation
	(
	OperationID bigint IDENTITY(1,1) NOT NULL,
	CategoryID bigint NOT NULL,
	Description nvarchar(50) NOT NULL,
	HouseHoldID bigint NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Operation ADD CONSTRAINT
	PK_Operation PRIMARY KEY CLUSTERED 
	(
	OperationID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.Operation ADD CONSTRAINT
	FK_Operation_OperationCategory FOREIGN KEY
	(
	CategoryID
	) REFERENCES dbo.OperationCategory
	(
	CategoryID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Operation ADD CONSTRAINT
	FK_Operation_AppUser FOREIGN KEY
	(
	HouseHoldID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table FieldWorkResource */
CREATE TABLE dbo.FieldWorkResource
	(
	FieldWorkResourceID bigint IDENTITY(1,1) NOT NULL,
	FieldWorkID bigint NOT NULL,
	ResourceID bigint NOT NULL,
	Quota decimal(19, 2) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWorkResource ADD CONSTRAINT
	PK_FieldWorkResource PRIMARY KEY CLUSTERED 
	(
	FieldWorkResourceID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWorkResource ADD CONSTRAINT
	FK_FieldWorkResource_FieldWork FOREIGN KEY
	(
	FieldWorkID
	) REFERENCES dbo.FieldWork
	(
	FieldWorkID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.FieldWorkResource ADD CONSTRAINT
	FK_FieldWorkResource_Resource FOREIGN KEY
	(
	ResourceID
	) REFERENCES dbo.Resource
	(
	ResourceID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table FieldWorkOperation */
CREATE TABLE dbo.FieldWorkOperation
	(
	FieldWorkOperationID bigint IDENTITY(1,1) NOT NULL,
	FieldWorkID bigint NOT NULL,
	OperationID bigint NOT NULL,
	Cost decimal(19, 2) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWorkOperation ADD CONSTRAINT
	PK_FieldWorkOperation PRIMARY KEY CLUSTERED 
	(
	FieldWorkOperationID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.FieldWorkOperation ADD CONSTRAINT
	FK_FieldWorkOperation_FieldWork FOREIGN KEY
	(
	FieldWorkID
	) REFERENCES dbo.FieldWork
	(
	FieldWorkID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.FieldWorkOperation ADD CONSTRAINT
	FK_FieldWorkOperation_Operation FOREIGN KEY
	(
	OperationID
	) REFERENCES Operation
	(
	OperationID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO

/* Create table StorageTranaction */
CREATE TABLE dbo.StorageTranaction
	(
	StorageTranactionID bigint IDENTITY(1,1) NOT NULL,
	TransactionTypeID tinyint NOT NULL,
	Date datetime NOT NULL DEFAULT GETDATE(), 
	OperatorID bigint NOT NULL,
	ResourceID bigint NOT NULL,
	PricePerUnit decimal(19, 2) NOT NULL,
	Units decimal(19, 2) NOT NULL,
	TotalAmount decimal(19, 2) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.StorageTranaction ADD CONSTRAINT
	PK_StorageTranaction PRIMARY KEY CLUSTERED 
	(
	StorageTranactionID ASC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.StorageTranaction ADD CONSTRAINT
	FK_StorageTranaction_Resource FOREIGN KEY
	(
	ResourceID
	) REFERENCES dbo.Resource
	(
	ResourceID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.StorageTranaction ADD CONSTRAINT
	FK_StorageTranaction_AppUser FOREIGN KEY
	(
	OperatorID
	) REFERENCES dbo.AppUser
	(
	UserID
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
GO